package com.john.excel.reader;

import javax.swing.filechooser.FileSystemView;
import java.io.File;
import java.util.List;

/**
 * 读取Excel工具类
 * @author john
 */
public class ExcelReader {
	
    //excel2003扩展名
	private static final String EXCEL03_EXTENSION = ".xls";
    //excel2007扩展名
	private static final String EXCEL07_EXTENSION = ".xlsx";

    /**
     * 每获取一条记录，即打印
     * 在flume里每获取一条记录即发送，而不必缓存起来，可以大大减少内存的消耗，这里主要是针对flume读取大数据量excel来说的
     * @param sheetName
     * @param sheetIndex
     * @param curRow
     * @param cellList
     */
    public static void sendRows(String filePath, String sheetName, int sheetIndex, int curRow,int rows, List<String> cellList) {
            StringBuffer oneLineSb = new StringBuffer();
            oneLineSb.append(filePath);
            oneLineSb.append("--");
            oneLineSb.append("sheet" + sheetIndex);
            oneLineSb.append("::" + sheetName);//加上sheet名
            oneLineSb.append("--");
            oneLineSb.append("rows" + rows);
            oneLineSb.append("::" + curRow);
            oneLineSb.append("::");
            for (String cell : cellList) {
                oneLineSb.append(cell.trim());
                oneLineSb.append("|");
            }
            String oneLine = oneLineSb.toString();
            if (oneLine.endsWith("|")) {
                oneLine = oneLine.substring(0, oneLine.lastIndexOf("|"));
            }// 去除最后一个分隔符

            System.out.println(oneLine);
    }

    /**
     * 读取Excel 默认会跳过第一行(标题行),读取索引sheet
     * @param filePath 地址 C:\Users\john\Desktop\品名批量翻译及处理.xlsx
     * @param callback 回调
     * @throws Exception
     */
    public static Integer readExcel(String filePath,ExcelReaderCallback callback) throws Exception {
        int totalRows =0;
        if (filePath.endsWith(EXCEL03_EXTENSION)) { //处理excel2003文件
            ExcelXlsReader excelXls=new ExcelXlsReader();
            totalRows =excelXls.parse(filePath,callback);
        } else if (filePath.endsWith(EXCEL07_EXTENSION)) {//处理excel2007文件
            ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader();
            totalRows = excelXlsxReader.parse(filePath, callback);
        } else {
            throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。");
        }
        System.out.println("发送的总行数：" + totalRows);
        return totalRows;
    }
    
    /**
     * 读取Excel
     * @param filePath	地址 C:\Users\john\Desktop\品名批量翻译及处理.xlsx
     * @param sheetId sheet索引 从1开始
     * @param startRowIndex 起始读取行号 从1开始
     * @param callback	回调
     * @throws Exception
     */
    public static Integer readExcel(String filePath,int sheetId, int startRowIndex,ExcelReaderCallback callback) throws Exception {
        int totalRows =0;
        if (filePath.endsWith(EXCEL03_EXTENSION)) { //处理excel2003文件
            ExcelXlsReader excelXls=new ExcelXlsReader();
            totalRows =excelXls.parse(filePath,callback);
        } else if (filePath.endsWith(EXCEL07_EXTENSION)) {//处理excel2007文件
            ExcelXlsxReader excelXlsxReader = new ExcelXlsxReader();
            totalRows = excelXlsxReader.parse(filePath, sheetId, startRowIndex,callback);
        } else {
            throw new Exception("文件格式错误，fileName的扩展名只能是xls或xlsx。");
        }
        callback.endParse(totalRows);
        System.out.println("发送的总行数：" + totalRows);
        return totalRows;
    }

    public static void main(String[] args) throws Exception {
    	File desktopDir = FileSystemView.getFileSystemView().getHomeDirectory();
		String desktopPath = desktopDir.getAbsolutePath();
		String filePath = desktopPath + "\\" + "784-22978723.xlsx";
		ExcelReader.readExcel(filePath,new ExcelReaderCallback() {
			@Override
			public void getResult(ExcelReadrResult resultExcel) {
				sendRows(resultExcel.getFilePath(), resultExcel.getSheetName(), resultExcel.getSheetIndex(), resultExcel.getCurRow(),resultExcel.getRows(),resultExcel.getCellList());
			}

			@Override
			public void endParse(Integer rows) {
				System.out.println("解析结束..."+rows);
			}
		});
    }
}